package siestageek;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class J03_MemberDAO 
				implements J03_IMemberDAO {

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:siestageek/siestageek@112.216.253.125:1521:xe";
	private String insertSQL = " insert into J03MEMBER values ( exam.nextval, ?, ?, ?, ?, sysdate ) ";
	private String selectSQL = " select * from J03Member order by mno desc ";
	private String updateSQL = " update J03MEMBER set passwd = ?, name = ?, email = ?, regdate=sysdate where mno = ? "    ;
	private String deleteSQL = "delete J03MEMBER where mno = ? ";
			
	@Override
	public boolean insertMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
				// ? 매개변수에 대입할 값 지정
				pstmt.setString( 1, m.getUserid() );
				pstmt.setString( 2, m.getPasswd() );
				pstmt.setString( 3, m.getName() );
				pstmt.setString( 4, m.getEmail() );
				
			pstmt.executeUpdate();
			result = true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}	// insertMember
	
	
	
	// JDBC Conenction 객체를 생성하는 메서드
	private Connection openDatabase() {
		try {
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		} catch (Exception ex) {
			ex.printStackTrace();
		} 
		return null;
	}
	
	// JDBC 객체를 닫는 메서드
	private void closeDatabase( Connection c, 
			PreparedStatement p, ResultSet r ) {
		if ( r != null ) try { r.close(); r = null; } catch(Exception e) { }
		if ( p != null ) try { p.close(); p = null; } catch(Exception e) { }
		if ( c != null ) try { c.close(); c = null; } catch(Exception e) { }
	}

	// select 문으로 전체 회원정보를 조회한 후
	// MemberVO를 내용으로 가지는
	// 동적배열 List로 결과를 넘김
	@Override
	public List<J03_MemberVO> selectMember() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<J03_MemberVO> result = new ArrayList<>();
		
		try {
			conn = openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while( rs.next() ) {
				J03_MemberVO m = new J03_MemberVO();
					m.setMno( rs.getString( "mno" ) );
					m.setUserid( rs.getString( "userid" ) );
					m.setPasswd( rs.getString( "passwd" ) );
					m.setName( rs.getString( "name" ) );
					m.setEmail( rs.getString( "email" ) );
					m.setRegdate( rs.getString( "regdate" ) );					
				result.add( m );
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeDatabase(conn, pstmt, rs);
		}
		
		return result;
	}



	@Override
	public boolean updateMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = openDatabase();
			pstmt = conn.prepareStatement(updateSQL);
				pstmt.setString( 1, m.getPasswd() );
				pstmt.setString( 2, m.getName() );
				pstmt.setString( 3, m.getEmail() );
				pstmt.setString( 4, m.getMno() );
			int cnt = pstmt.executeUpdate();
			if (cnt == 0) System.out.println( 
				"명령은 잘 실행됐지만, 수정된 데이터는 없음" ); 
			result = true;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}



	@Override
	public boolean deleteMember(J03_MemberVO m) {
		// TODO Auto-generated method stub
		return false;
	}
	
	
	
	
	
	
	
	
	
	

}
